A declarative database query language, such as structured query language (“SQL”), instructs a database management system (“DBMS”) to retrieve certain rows and columns of data. Data may also be retrieved from other sources (e.g., flat file, XML file, etc.) using other programming languages. Code generators may be used to analyze high-level execution plans and produce computer code corresponding thereto. For example, if SQL is produced, the SQL may be executed by a runtime engine of a DBMS. Such code generators are useful in environments where the need for long or computationally complex query code arises frequently.
In an SQL environment, one approach to generating large database queries uses inner queries, otherwise known as “nested queries,” within the main query. Such approach may also be implemented in other computer language environments using nested functions. In SQL, nested queries produce intermediate output that is further accessed by the larger query confining them. The advantages of using nested queries include their speed. Nested queries are almost exclusively executed in memory and a DBMS is typically equipped with a query optimizer. In another approach, intermediate output is stored in temporary storage from which it is later extracted from subsequent queries. A temporary storage approach may require creating, querying and then dropping a storage table. While this approach is inherently slower, temporary tables have some advantages over nested queries. Temporary tables may be preferred for fault-recovery or debugging purposes, since the tables retain a snapshot of intermediate output produced at several stages of the entire query.